var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('fileDB.db');

var id = function generateUUID() {
	var d = new Date().getTime();
	var uuid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function (c) {
		var r = (d + Math.random() * 16) % 16 | 0;
		d = Math.floor(d / 16);
		return (c == 'x' ? r : (r & 0x7 | 0x8)).toString(16);
	});
	return uuid;
};

router.get('/files', function (req, res, next) {

	db.all("SELECT fileID, filePath, fileName FROM fileTable ", function (err, rows) {
		if (!err) {
			res.json(rows);
		} else {
			res.status(500).json(err);
		}
	});

});

router.get('/files/:filePath', function (req, res, next) {

	db.all("SELECT fileID, filePath, fileName FROM fileTable WHERE filePath like '" + req.params.filePath + "%'", function (err, rows) {
		if (!err) {
			res.json(rows);
		} else {
			res.status(500).json(err);
		}
	});

});


router.get('/file/:fileID', function (req, res, next) {

	var result;
	console.log('retrieveFileByID');
	db.all("SELECT fileID, filePath, fileName, fileCnt FROM fileTable WHERE fileID = '" + req.params.fileID + "'", function (err, rows) {
		if (!err) {
			res.json(rows);
		} else {
			res.status(500).json(err);
		}
	});
});

router.post('/file', function (req, res, next) {

	console.log('createFile');
	let stmt = db.prepare('INSERT INTO fileTable VALUES (?,?,?,?)');
	let fileID = id();
	stmt.run(fileID, req.body.filePath, req.body.fileName, req.body.fileCnt, function (err) {
		if (err) {
			res.status(500).json(err);
		} else {
			db.all("SELECT fileID, filePath, fileName, fileCnt FROM fileTable WHERE fileID = '" + fileID + "'", function (err, rows) {
				if (!err) {
					res.json(rows);
				} else {
					res.status(500).json(err);
				}
			});
		}
	});
	stmt.finalize();

});


router.put('/file/:fileID', function (req, res, next) {

	db.all("SELECT fileID FROM fileTable WHERE fileID = '" + req.params.fileID + "'", function (err, rows) {
		if (!err) {
			if (rows && rows.length === 0) {
				let stmt = db.prepare('INSERT INTO fileTable VALUES (?,?,?,?)');
				stmt.run(req.body.fileID, req.body.filePath, req.body.fileName, req.body.fileCnt, function (err) {
					if (err) {
						res.status(500).json(err);
					} else {
						db.all("SELECT fileID, filePath, fileName, fileCnt FROM fileTable WHERE fileID = '" + req.params.fileID + "'", function (err, rows) {
							if (!err) {
								res.json(rows);
							} else {
								res.status(500).json(err);
							}
						});
					}
				});
				stmt.finalize();
			} else {
				let stmt = db.prepare('UPDATE fileTable SET filePath=?, fileName=?, fileCnt=? WHERE fileID=?');
				stmt.run(req.body.filePath, req.body.fileName, req.body.fileCnt, req.body.fileID, function (err) {
					if (err) {
						res.status(500).json(err);
					} else {
						db.all("SELECT fileID, filePath, fileName, fileCnt FROM fileTable WHERE fileID = '" + req.params.fileID + "'", function (err, rows) {
							if (!err) {
								res.json(rows);
							} else {
								res.status(500).json(err);
							}
						});
					}
				});
				stmt.finalize();
			}

		} else {
			res.status(500).json(err);
		}
	});

});

router.delete('/file/:fileID', function (req, res, next) {

	let stmt = db.prepare('DELETE FROM fileTable WHERE fileID = ? ');
	stmt.run(req.params.fileID);
	stmt.finalize();
	db.all("SELECT fileID, filePath, fileName, fileCnt FROM fileTable WHERE fileID = '" + req.params.fileID + "'", function (err, rows) {
		if (!err) {
			res.json(rows);
		} else {
			res.status(500).json(err);
		}
	});
});

router.delete('/files', function (req, res, next) {

	let stmt = db.prepare('DELETE FROM fileTable WHERE filePath like ? ');
	stmt.run(req.query.filePath + "%");
	stmt.finalize();
	db.all("SELECT fileID, filePath, fileName, fileCnt FROM fileTable WHERE filePath like '" + req.query.filePath + "%'", function (err, rows) {
		if (!err) {
			res.json(rows);
		} else {
			res.status(500).json(err);
		}
	});
});


module.exports = router;



